const express = require('express');
const router = express.Router();
const pool = require('./db.js');
const multer = require('multer');
const XLSX = require('xlsx');
const { decryptForUser } = require('./userDataEncryption.js'); // 导入解密函数

const storage = multer.memoryStorage();
const upload = multer({ storage: storage });

// 创建班级（无需修改）
router.post('/create-class', async (req, res) => {
    const { classCode, className, classCreatorId } = req.body;

    try {
        const [rows] = await pool.execute('SELECT * FROM classes WHERE class_code =?', [classCode]);
        if (rows.length > 0) {
            return res.json({ message: '班级编号已存在' });
        }

        await pool.execute('INSERT INTO classes (class_code, class_name, class_creator_id) VALUES (?,?,?)', [classCode, className, classCreatorId]);
        res.json({ message: '班级创建成功' });
    } catch (error) {
        console.error('Error creating class:', error);
        res.status(500).json({ message: '服务器错误' });
    }
});

// 检查班级编号是否已存在（无需修改）
router.get('/check-class-code', async (req, res) => {
    const { classCode } = req.query;
    try {
        const [rows] = await pool.execute('SELECT * FROM classes WHERE class_code =?', [classCode]);
        res.json({ exists: rows.length > 0 });
    } catch (error) {
        console.error(error);
        res.status(500).json({ message: '服务器错误' });
    }
});

// 检查用户是否已加入班级（无需修改）
router.get('/check-user-in-class', async (req, res) => {
    const { userId } = req.query;
    
    try {
        const [rows] = await pool.execute('SELECT * FROM class_attention WHERE user_id = ?', [userId]);
        res.json({ inClass: rows.length > 0 });
    } catch (error) {
        console.error('检查用户是否已加入班级失败:', error);
        res.status(500).json({ message: '服务器错误' });
    }
});

// 加入班级（无需修改）
router.post('/join-class', async (req, res) => {
    const { userId, classCode } = req.body;
    
    try {
        const [checkRows] = await pool.execute('SELECT * FROM class_attention WHERE user_id = ?', [userId]);
        if (checkRows.length > 0) {
            return res.json({ success: false, message: '你已经加入了一个班级' });
        }
        
        await pool.execute('INSERT INTO class_attention (user_id, class_code) VALUES (?, ?)', [userId, classCode]);
        res.json({ success: true, message: '加入班级成功' });
    } catch (error) {
        console.error('加入班级失败:', error);
        res.status(500).json({ success: false, message: '服务器错误' });
    }
});

// 获取教师创建的班级（无需修改）
router.get('/get-teacher-classes', async (req, res) => {
    const { teacherUserId } = req.query;
    
    try {
        const [rows] = await pool.execute('SELECT * FROM classes WHERE class_creator_id = ?', [teacherUserId]);
        res.json(rows);
    } catch (error) {
        console.error('获取教师班级失败:', error);
        res.status(500).json({ message: '服务器错误' });
    }
});

// 获取班级成员（需要修改）
router.get('/get-class-members', async (req, res) => {
    const { classCode } = req.query;
    
    try {
        // 1. 获取加密的用户数据
        const [rows] = await pool.execute(`
            SELECT u.user_id, u.student_id, u.name, u.username 
            FROM class_attention ca 
            JOIN users u ON ca.user_id = u.user_id 
            WHERE ca.class_code = ?
        `, [classCode]);
        
        // 2. 解密用户数据
        const decryptedMembers = rows.map(user => {
            return {
                user_id: user.user_id,
                student_id: user.student_id ? decryptForUser(user.user_id, user.student_id) : '',
                name: user.name ? decryptForUser(user.user_id, user.name) : '',
                username: user.username ? decryptForUser(user.user_id, user.username) : ''
            };
        });
        
        res.json(decryptedMembers);
    } catch (error) {
        console.error('获取班级成员失败:', error);
        res.status(500).json({ message: '服务器错误' });
    }
});

// 移除班级成员（无需修改）
router.post('/remove-class-member', async (req, res) => {
    const { classCode, userId } = req.body;
    
    try {
        const [result] = await pool.execute('DELETE FROM class_attention WHERE class_code = ? AND user_id = ?', [classCode, userId]);
        
        if (result.affectedRows > 0) {
            res.json({ success: true, message: '移除成功' });
        } else {
            res.json({ success: false, message: '成员不存在' });
        }
    } catch (error) {
        console.error('移除班级成员失败:', error);
        res.status(500).json({ success: false, message: '服务器错误' });
    }
});

// 导入班级成员（需要修改）
router.post('/import-class-members', upload.single('file'), async (req, res) => {
    const { classCode } = req.body;
    const file = req.file;

    if (!classCode) {
        return res.status(400).json({ success: false, message: '班级编号不能为空' });
    }

    if (!file) {
        return res.status(400).json({ success: false, message: '未上传文件' });
    }

    try {
        // 1. 读取 Excel 文件
        const workbook = XLSX.read(file.buffer, { type: 'buffer' });
        const sheetName = workbook.SheetNames[0];
        const worksheet = workbook.Sheets[sheetName];
        const jsonData = XLSX.utils.sheet_to_json(worksheet, { header: 1 });

        // 2. 验证表头
        if (jsonData.length < 2 || jsonData[0][0]!== '学号' || jsonData[0][1]!== '姓名') {
            return res.status(400).json({ success: false, message: '文件格式不正确，表头应为"学号"和"姓名"' });
        }

        // 3. 获取所有学生用户（解密后比较）
        const [allStudents] = await pool.execute(
            'SELECT user_id, student_id, name FROM users WHERE role = "学生"'
        );
        
        // 4. 解密学生数据并创建映射
        const studentMap = new Map();
        allStudents.forEach(student => {
            const decryptedStudentId = student.student_id ? decryptForUser(student.user_id, student.student_id) : '';
            const decryptedName = student.name ? decryptForUser(student.user_id, student.name) : '';
            
            if (decryptedStudentId && decryptedName) {
                const key = `${decryptedStudentId}-${decryptedName}`;
                studentMap.set(key, student.user_id);
            }
        });

        // 5. 处理Excel数据行
        const validMembers = [];
        for (let i = 1; i < jsonData.length; i++) {
            const studentId = jsonData[i][0];
            const name = jsonData[i][1];

            if (!studentId || !name) continue;

            // 使用学号+姓名作为键查找用户
            const key = `${studentId}-${name}`;
            if (studentMap.has(key)) {
                validMembers.push(studentMap.get(key));
            }
        }

        // 6. 过滤已存在的成员
        const newMembers = [];
        for (const userId of validMembers) {
            const [existingRows] = await pool.execute(
                'SELECT * FROM class_attention WHERE user_id = ? AND class_code = ?',
                [userId, classCode]
            );
            if (existingRows.length === 0) {
                newMembers.push(userId);
            }
        }

        // 7. 批量插入新成员
        if (newMembers.length > 0) {
            const values = newMembers.map(userId => [userId, classCode]);
            const placeholders = values.map(() => '(?, ?)').join(', ');
            const sql = `INSERT INTO class_attention (user_id, class_code) VALUES ${placeholders}`;
            await pool.execute(sql, values.flat());
        }

        res.json({ success: true, message: `成功导入${newMembers.length}名班级成员` });
    } catch (error) {
        console.error('导入班级成员失败:', error);
        res.status(500).json({ success: false, message: '导入失败', error: error.message });
    }
});

// 删除班级（无需修改）
router.post('/delete-class', async (req, res) => {
    const { classCode } = req.body;
    
    try {
        const conn = await pool.getConnection();
        
        try {
            await conn.query('START TRANSACTION');
            await conn.execute('DELETE FROM class_attention WHERE class_code = ?', [classCode]);
            const [result] = await conn.execute('DELETE FROM classes WHERE class_code = ?', [classCode]);
            await conn.query('COMMIT');
            
            if (result.affectedRows > 0) {
                res.json({ success: true, message: '班级删除成功' });
            } else {
                res.json({ success: false, message: '班级不存在' });
            }
        } catch (error) {
            await conn.query('ROLLBACK');
            throw error;
        } finally {
            conn.release();
        }
    } catch (error) {
        console.error('删除班级失败:', error);
        res.status(500).json({ success: false, message: '服务器错误' });
    }
});

module.exports = router;